Control Flow

All programming languages must provide one or more ways to execute some statements out of the sequence in which they appear in the program listing. Apart from calls to Sub and Function procedures, you can gather all the basic control flow statements in two groups: branch statements and loop statements.

The main branch statement is the If...Else...Else If...End If block. Visual Basic supports several flavors of this statement, including single-line and multiline versions:

' Single line version, without Else clause If x > 0 Then y = x ' Single line version, with Else clause If x > 0 Then y = x Else y = 0 ' Single line, but with multiple statements separated by colons If x > 0 Then y = x: x = 0 Else y = 0 ' Multiline version of the above code (more readable) If x > 0 Then y = x x = 0 Else y = 0 End If ' An example of If..ElseIf..Else block If x > 0 Then y = x ElseIf x < 0 Then y = x * x Else ' X is surely 0, no need to actually test it. x = -1 End If

You should be aware that any nonzero value after the If keyword is considered to be True and therefore fires the execution of the Then block:

' The following lines are equivalent. If value <> 0 Then Print "Non Zero" If value Then Print "Non Zero"

Even if this latter notation lets you save some typing, you shouldn't believe that it also makes your program faster, at least not necessarily. Benchmarks show that if the variable being tested is of type Boolean, Integer, or Long, this shortened notation doesn't make your program run faster. With other numeric types, however, you can expect some modest speed increment, about 20 percent or less. If you feel comfortable with this technique, go ahead and use it, but be aware that in many cases the speed improvement isn't worth the decreased readability.

Many advanced optimization techniques become possible when you combine multiple conditions using AND and OR operators. The following examples show how you can often write more concise and efficient code by rewriting a Boolean expression:

' If two numbers are both zero, you can apply the OR operator ' to their bits and you still have zero. If x = 0 And y = 0 Then ... If (x Or y) = 0 Then ... ' If either value is <>0, you can apply the OR operator ' to their bits and you surely have a nonzero value. If x <> 0 Or y <> 0 Then ... If (x Or y) Then ... ' If two integer numbers have opposite signs, applying the XOR ' operator to them yields a result that has the sign ' bit set. (In other words, it is a negative value.) If (x < 0 And y >= 0) Or (x >= 0 And y < 0) Then ... If (x Xor y) < 0 Then ...

It's easy to get carried away when you're working with Boolean operators and inadvertently introduce subtle bugs into your code. For example, you might believe that the following two lines of code are equivalent, but they aren't. (To understand why, just think how numbers are represented in binary.)

' Not equivalent: just try with x=3 and y=4, whose binary ' representations are 0011 and 0100 respectively. If x <> 0 And y <> 0 Then ... If (x And y) Then ... ' Anyway, you can partially optimize the first line as follows: If (x <> 0) And y Then ...

Another frequent source of ambiguity is the NOT operator, which toggles all the bits in a number. In Visual Basic, this operator returns False only if its argument is True (-1), so you should never use it with anything except the Boolean result of a comparison or with a Boolean variable:

If Not (x = y) Then ... ' The same as x<>y If Not x Then ... ' The same as x<>-1, don't use instead of x=0

For more information, see the section "Boolean and Bit-Wise Operators" later in this chapter.

One detail that surprises many programmers coming to Visual Basic from other languages is that the If statement doesn't support the so-called short-circuit evaluation. In other words, Visual Basic always evaluates the whole expression in the If clause, even if it has enough information to determine that it is False or True, as in the following code:

' If x<=0, it makes no sense to evaluate Sqr(y)>x ' because the entire expression is guaranteed to be False. If x > 0 And Sqr(y) < z Then z = 0 ' If x=0, it makes no sense to evaluate x*y>100. ' because the entire expression is guaranteed to be True. If x = 0 Or x * y > 100 Then z = 0

Even though Visual Basic isn't smart enough to optimize the expression automatically, it doesn't mean that you can't do it manually. You can rewrite the first If statement above as follows:

If x > 0 Then If Sqr(y) < z Then z = 0

You can rewrite the second If statement above as follows:

If x = 0 Then z = 0 ElseIf x * y > 100 Then z = 0 End If

The Select Case statement is less versatile than the If block in that it can test only one expression against a list of values:

Select Case Mid$(Text, i, 1) Case "0" To "9" ' It's a digit. Case "A" To "Z", "a" To "z" ' It's a letter. Case ".", ",", " ", ";", ":", "?" ' It's a punctuation symbol or a space. Case Else ' It's something else. End Select

The most effective optimization technique with the Select Case block is to move the most frequent cases toward the top of the block. For instance, in the previous example you might decide to test whether the character is a letter before testing whether it's a digit. This change will slightly speed up your code if you're scanning regular text that's expected to contain more words than numbers.

Surprisingly, the Select Case block has an interesting feature that's missing in the more flexible If statement—namely, the ability to perform short circuit evaluation, sort of. In fact, Case subexpressions are evaluated only until they return True, after which all the remaining expressions on the same line are skipped. For example, in the Case clause that tests for punctuation symbols in the preceding code snippet, if the character is a dot all the other tests on that line are never executed. You can exploit this interesting feature to rewrite (and optimize) some complex If statements composed of multiple Boolean subexpressions:

' This series of subexpressions connected by the AND operator: If x > 0 And Sqr(y) > x And Log(x) < z Then z = 0 ' can be rewritten as: Select Case False Case x > 0, Sqr(y) > x, Log(x) < z ' Do nothing if any of the above meets the condition, ' that is, is False. Case Else ' This is executed only if all the above are True. z = 0 End Select ' This series of subexpressions connected by the OR operator: If x = 0 Or y < x ^ 2 Or x * y = 100 Then z = 0 ' can be rewritten as: Select Case True Case x = 0, y < x ^ 2, x * y = 100 ' This is executed as soon as one of the above is found ' to be True. z = 0 End Select

As it is for similarly unorthodox optimization techniques, my suggestion is to thoroughly comment your code, explaining what you're doing and always including the original If statement as a remark. This technique is highly effective for speeding up portions of your code, but you should never forget that optimization isn't all that important if you're going to forget what you did or if your code looks obscure to colleagues who have to maintain it.

Then comes the GoTo statement, deemed to be the main cause of tons of spaghetti code that plagues many applications. I must admit, however, that my attitude toward this four-letter keyword isn't so negative. In fact, I still prefer one single GoTo statement to a chain of Exit Do or Exit For statements for getting out of a series of nested loops. I suggest this: Use the GoTo statement as an exception to the regular flow of execution, and always use significant label names and meaningful remarks all over the code to explain what you're doing.

The GoSub…Return keyword pair is a little bit better than GoTo because it's more structured. In some cases, using GoSub to call a piece of code inside the current procedure is better than calling an external Sub or Function. You can neither pass arguments nor receive return values; but, on the other hand, the called code shares all the parameters and local variables with your current procedure, so in most cases you don't need to pass anything. You should be aware, however, that when you compile to native code, the GoSub keyword is about 6 to 7 times slower than a call to an external procedure in the same module, so always benchmark the two approaches if you're writing time-critical code.

The most frequently used looping structure in Visual Basic is undoubtedly the For...Next loop:

For counter = startvalue To endvalue [Step increment] ' Statements to be executed in the loop... Next

You need to specify the Step clause only if increment is not equal to 1. You can exit the loop using an Exit For statement, but unfortunately Visual Basic doesn't provide any sort of "Repeat" command that lets you skip the remaining part of the current iteration and restart the loop. The best you can do is use (nested) If statements or, if you don't want to make the logic too complex, use a plain GoTo keyword that points to the end of the loop. In fact, this is one of the few occasions when a single GoTo statement can make your code more readable and maintainable:

For counter = 1 To 100 ' Do your stuff here ... ' if you want to skip over what follows, just GoTo NextLoop. If Err Then Goto NextLoop ' more code that you don't want to enclose within nested IF blocks ' ... NextLoop: Next

When you want to be absolutely sure that a loop is executed a given number of times, use an integer controlling variable and explicitly increment the floating-point variable within the loop:

Dim d As Single, count As Long ' Scale start and end values by a factor of 10 ' so that you can use integers to control the loop. For count = 0 To 10 ' Do what you want with the D variable, and then increment it ' to be ready for the next iteration of the loop. d = d + 0.1 Next

I covered the For Each...Next loop already in Chapter 4, and I won't repeat its description here. I just want to show you a neat trick that's based on this type of loop and the Array function. This technique permits you to execute a block of statements with different values for a controlling variable, which don't need to be in sequence:

' Test if Number can be divided by any of the first 10 prime numbers. Dim var As Variant, NotPrime As Boolean For Each var In Array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29) If (Number Mod var) = 0 Then NotPrime = True: Exit For Next

The values don't even have to be numeric:

' Test if SourceString contains the strings "one", "two", "three", etc. Dim var2 As Variant, MatchFound As Boolean For Each var2 In Array("one", "two", "three", "four", "five") If InStr(1, SourceString, var2, vbTextCompare) Then MatchFound = True: Exit For End If Next

The Do...Loop structure is more flexible than the For...Next loop in that you can place the termination test either at the beginning or the end of the loop. (In the latter case, the loop is always executed at least once.) You can use either the While clause (repeat while the test condition is True) or the Until clause (repeat while the test condition is False). You can exit a Do loop at any moment by executing an Exit Do statement, but—as with For...Next loops—VBA doesn't offer a keyword that skips over the remaining statements in the loop and immediately restarts the loop.

' Example of a Do loop with test condition at the top. ' This loop is never executed if x <= 0. Do While x > 0 y = y + 1 x = x \ 2 Loop ' Example of a Do loop with test condition at the bottom. ' This loop is always executed at least once, even if x <= 0. Do y = y + 1 x = x \ 2 Loop Until x <= 0 ' Endless loop: requires an Exit Do statement to get out. Do ... Loop

The While...Wend loop is conceptually similar to the Do While...Loop. But you can test the condition only at the beginning of the loop, you don't have an Until clause, and you don't even have an Exit While command. For these reasons, most programmers prefer the more flexible Do...Loop structure, and in fact you won't see a single While...Wend loop in this entire book.

A few VBA functions are closely related to control flow, even if by themselves they don't alter the execution flow. The IIf function, for example, can often replace an If...Else...End If block, as in the following code:

' These lines are equivalent. If x > 0 Then y = 10 Else y = 20 y = IIf(x > 0, 10, 20)

The Choose function lets you select a value in a group; you can use it to distinguish among three or more cases. So, instead of this code:

' The classic three-choices selection If x > y Then Print "X greater than Y" ElseIf x < y Then Print "X less than Y" Else Print "X equals Y" End If

you can use this shorter version:

' Shortened form, based on Sgn() and Choose() functions. ' Note how you keep the result of Sgn() in the range 1-3. Print "X " & Choose(Sgn(x _ y) + 2, "less than", "equals", _ "greater than") & " Y"

The Switch function accepts a list of (condition, value) pairs and returns the first value that corresponds to a condition that evaluates as True. See, for example, how you can use this function to replace this Select Case block:

Select Case x Case Is <= 10: y = 1 Case 11 To 100: y = 2 Case 101 To 1000: y = 3 Case Else: y = 4 End Select

Same effect in just one line.

' The last "True" expression replaces the "Else" clause. y = Switch(x <= 10, 1, x <= 100, 2, x <= 1000, 3, True, 4)

You should remember two things when you're using this function: First, if none of the expressions returns a True value, the Switch function returns Null. Second, all the expressions are always evaluated, even though only one value is returned. For these reasons, you might get unexpected errors or undesired side effects. (For example, if one expression raises an overflow or division-by-zero error.)